package com.wanlian.dao.impl;

import com.wanlian.common.IntRmb;
import com.wanlian.common.PageCondition;
import com.wanlian.dao.UserScoreDao;
import com.wanlian.dao.WordBookDao;
import com.wanlian.entity.ScoreHistory;
import com.wanlian.entity.UserScore;
import com.wanlian.entity.WordBook;
import com.wanlian.model.ResultModel;
import com.wanlian.util.DateUtil;
import com.wanlian.vo.Page;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class UserScoreDaoImpl extends GenericDaoImpl<UserScore> implements UserScoreDao {
    @Autowired
    public WordBookDao wordBookDao;

    public UserScoreDaoImpl() {
        super(UserScore.class);
    }

    @Override
    public ResultModel queryUserScore(Page page, Map<String, Object> conditions) {
        return null;
    }

    @Override
    public UserScore queryUserScoreByUserId(int userId) {
        String hql = String.format("from %s where userId = ?", entityClassName());
        UserScore userScore = this.queryForObject(hql, new Object[]{userId});
        return userScore;
    }


    @Override
    public UserScore queryUserScoreByParams(int userId, int rankType) {
        String hql = String.format("from %s where userId = ? and rankType=? ", entityClassName());
        UserScore userScore = this.queryForObject(hql, new Object[]{userId, rankType});
        return userScore;
    }

    @Override
    public ResultModel queryUserScoreByParams(PageCondition pageCondition, int uid) {

        float int_rb = IntRmb.intRmb();

        WordBook in_wordBook = wordBookDao.queryWordBookByVal2("intnum_proportion");
        float intnum_proportion = Float.parseFloat(in_wordBook.getWbval1());


        WordBook dn_wordBook = wordBookDao.queryWordBookByVal2("dxnum_proportion");
        float dxnum_proportion = Float.parseFloat(dn_wordBook.getWbval1());

        WordBook bl_wordBook = wordBookDao.queryWordBookByVal2("int_proportion");
        float intBL = Float.parseFloat(bl_wordBook.getWbval1());

        String[] column = {"id", "phone", "username", "dscore", "iscore", "score", "rmb", "intnum"};

        String sql = "select  u.id as uid,u.phone,u.username," +
                " ifnull( sd.dscore ,0)," +
                " ifnull(si.iscore,0) ," +
                " ifnull(s.score,0)," +
                " ifnull(truncate(si.iscore*" + intnum_proportion + ",6),0)*" + int_rb + "+ifnull(truncate(sd.dscore*" + dxnum_proportion + ",6),0) as rmb ," +
                " ifnull(truncate(si.iscore*" + intnum_proportion + ",6),0) as intnum " +
                " from tb_user u   " +
                " left join  " +
                " (select u.id  as uid,s.score as dscore from tb_user u left join tb_score s on u.id =s.userid where ranktype =1 ) sd " +
                " on sd.uid =u.id " +
                " left join " +
                " (select u.id  as uid,s.score as iscore from tb_user u left join tb_score s on u.id =s.userid where ranktype =2 ) si" +
                " on si.uid =u.id  " +
                " left join " +
                " (select u.id  as uid,sum(s.score) as  score from tb_user u left join tb_score s on u.id =s.userid   group by u.id)  s" +
                " on s.uid =u.id where 1=1";
        StringBuilder sqlWhere = new StringBuilder();

        ArrayList<Object> param = new ArrayList<Object>();

        if (uid != 0) {
            sqlWhere.append(" and u.id=?  ");
            param.add(uid);
        }

        if (pageCondition.isEmpty("username")) {
            String username = (String) pageCondition.getByKey("username");
            sqlWhere.append(" and u.username = ?");
            param.add(username);
        }
        if (pageCondition.isEmpty("phone")) {
            String phone = (String) pageCondition.getByKey("phone");
            sqlWhere.append(" and  u.phone = ? ");
            param.add(phone);
        }

        sqlWhere.append(" order by rmb desc ");

        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql + sqlWhere + "", param.toArray(), pageCondition.getPage(), column);
        int count = this.queryCountByParam("select count(1) from(" + sql + sqlWhere + ")v ", param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }

    @Override
    public ResultModel queryUserScoreHistoryByParams(PageCondition pageCondition, int uid) {
        String sql = "select u.username,sh.addtime,sh.ranktype,sh.score from tb_scorehistory sh,tb_user u where u.id=sh.userid and u.isenable=true  ";
        StringBuilder sqlWhere = new StringBuilder();
        String[] column = {"username", "addTime", "rankType", "score"};
        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isEmpty("startTime") && !pageCondition.isEmpty("endTime")) {
            String startTime = (String) pageCondition.getByKey("startTime");
            sqlWhere.append(" and sh.addtime > date(?)  ");
            param.add(startTime);
        } else if (!pageCondition.isEmpty("startTime") && pageCondition.isEmpty("endTime")) {
            String endTime = (String) pageCondition.getByKey("endTime");
            sqlWhere.append(" and sh.addtime < date(?)  ");
            param.add(endTime);
        } else if (pageCondition.isEmpty("startTime") && pageCondition.isEmpty("endTime")) {
            String startTime = (String) pageCondition.getByKey("startTime");
            String endTime = (String) pageCondition.getByKey("endTime");
            sqlWhere.append(" and sh.addtime between date(?) and date(?) ");
            param.add(startTime);
            param.add(endTime);
        }
        if (uid != 0) {
            //app端
            sqlWhere.append(" and sh.userid=?");
            param.add(uid);
        } else {
            if (pageCondition.isEmpty("uid")) {
                int id = Integer.parseInt((String) pageCondition.getByKey("uid"));
                sqlWhere.append(" and sh.userid=?");
                param.add(id);
            }
        }

        if (pageCondition.isEmpty("rankType")) {
            int rankType = Integer.parseInt((String) pageCondition.getByKey("rankType"));
            sqlWhere.append(" and sh.rankType=?");
            param.add(rankType);
        }
        sqlWhere.append(" order by sh.addtime desc ");
        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql + sqlWhere + "", param.toArray(), pageCondition.getPage(), column);
        int count = this.queryCountByParam("select count(1) from tb_scorehistory sh,tb_user u where u.id=sh.userid and u.isenable=true " + sqlWhere, param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }


    @Override
    public ResultModel queryUserScoreOrder(PageCondition pageCondition) {
        String sql = "select sum(s.score) scores,u.headimg,u.username from tb_score s,tb_user u where score>0 and s.userid=u.id ";
        StringBuilder sqlWhere = new StringBuilder();
        String[] column = {"score", "headimg", "username"};

        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isEmpty("date")) {
            String date = (String) pageCondition.getByKey("date");
            if (date.equals("week")) {
                sqlWhere.append(" and   s.addtime >  (SELECT DATE_FORMAT(DATE_SUB(DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY),INTERVAL 1 WEEK),'%Y-%m-%d 00:00:00'))" +
                        " and   s.addtime <  (SELECT DATE_FORMAT(SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59')) ");
            }
            if (date.equals("month")) {
                sqlWhere.append(" and   s.addtime > (SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')) " +
                        " and   s.addtime < (SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')) ");
            }
            /*if(date.equals("all")){
                sqlWhere.append("and   s.addtime >date_add(now(), interval -7 day)");
            }*/
        }
        String str = " group by u.username,u.headimg order by scores desc ";

        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql + sqlWhere + str, param.toArray(), pageCondition.getPage(), column);
        int count = this.queryCountByParam("select count(1) from  ( select sum(s.score) scores,u.username from tb_scorehistory s,tb_user u where score>0 and s.userid=u.id " + sqlWhere + " group by u.username )a", param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }

    @Override
    public ResultModel queryUserSumScoreOrder(PageCondition pageCondition) {
        String sql = "select sum(s.score),u.headimg,u.username from tb_score s,tb_user u where u.id=s.userid  group by u.username,u.headimg  order by sum(s.score) desc  ";
        String[] column = {"score", "headimg", "username"};

        ArrayList<Object> param = new ArrayList<Object>();


        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql, param.toArray(), pageCondition.getPage(), column);
        int count = this.queryCountByParam("select count(1) from  ( " + sql + " )a", param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }

    @Override
    public void addOrUpdate(UserScore userScore) {
        UserScore dbUserScore = this.queryUserScoreByParams(userScore.getUserId(), userScore.getRankType());
        if (dbUserScore == null) {
            dbUserScore = new UserScore();
            dbUserScore.setUserId(userScore.getUserId());
            dbUserScore.setRankType(userScore.getRankType());
            dbUserScore.setScore(userScore.getScore());
            this.add(dbUserScore);
        } else {
            double scored = dbUserScore.getScore() + userScore.getScore();
            dbUserScore.setScore(scored);
            this.update(dbUserScore);
        }
    }

    @Override
    public ResultModel queryUserScoreWater(PageCondition pageCondition, int uid) {
        String sql = " select * from (" +
                "   SELECT s.id,s.trantype,u.id AS uid, u.phone, u.username,'积分兑换' as stype ,d.pname, s.addtime, s.score, s.ranktype" +
                "   FROM   tb_user u, tb_scorehistory s, tb_product d " +
                "   WHERE u.id = s.userid AND d.id = s.productid and d.ranktype=s.ranktype and s.trantype=3" +
                "   union all " +
                "   SELECT s.id,s.trantype,u.id AS uid, u.phone, u.username,'积分兑换' as stype ,CONCAT(\"兑换\",s.intnum+\"\",\"INT\") as pname, s.addtime, s.score, s.ranktype  " +
                "   FROM   tb_user u, tb_scorehistory s " +
                "    WHERE u.id = s.userid    and s.intnum>0 and s.trantype=4 " +
                "   union all" +
                "   SELECT s.id,s.trantype,u.id AS uid, u.phone, u.username,'共享奖励' as stype , CONCAT(\"设备ID：\",d.deviceid), s.addtime, s.score, s.ranktype" +
                "   FROM   tb_user u, tb_scorehistory s, tb_device d " +
                "   WHERE u.id = s.userid AND d.deviceid = s.deviceid  and s.trantype=1 " +
                "   union all" +
                "   SELECT s.id,s.trantype,u.id AS uid, u.phone, u.username,t.name as stype ,'' as  name, s.addtime, s.score, s.ranktype" +
                "   FROM   tb_user u, tb_scorehistory s, tb_task t " +
                "   WHERE u.id = s.userid AND t.id = s.taskid AND t.ranktype=s.ranktype and s.trantype=2" +
                "   union all " +
                "   select s.id,s.trantype,u.id as uid,u.phone,u.username,'线上预售奖励' as stype ,'' as name ,s.addtime ,s.score,s.ranktype " +
                "   FROM   tb_user u, tb_scorehistory  s" +
                "    WHERE u.id = s.userid  and s.trantype=5  " +
                "   )a where 1=1 ";
        StringBuilder sqlWhere = new StringBuilder();
        String[] column = {"id", "trantype", "uid", "phone", "username", "stype", "pname", "addtime", "score", "rankType"};
        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isEmpty("startTime") && !pageCondition.isEmpty("endTime")) {
            String startTime = (String) pageCondition.getByKey("startTime");
            sqlWhere.append(" and a.addtime > date(?)  ");
            param.add(startTime);
        } else if (!pageCondition.isEmpty("startTime") && pageCondition.isEmpty("endTime")) {
            String endTime = (String) pageCondition.getByKey("endTime");
            sqlWhere.append(" and a.addtime < date(?)  ");
            param.add(endTime);
        } else if (pageCondition.isEmpty("startTime") && pageCondition.isEmpty("endTime")) {
            String startTime = (String) pageCondition.getByKey("startTime");
            String endTime = (String) pageCondition.getByKey("endTime");
            sqlWhere.append(" and a.addtime between date(?) and date(?) ");
            param.add(startTime);
            param.add(endTime);
        }

        if (uid != 0) {
            sqlWhere.append(" and a.uid=?  ");
            param.add(uid);
        } else {
            if (pageCondition.isEmpty("userId")) {
                uid = Integer.parseInt((String) pageCondition.getByKey("userId"));
                sqlWhere.append(" and a.uid=?  ");
                param.add(uid);
            }

        }

        if (pageCondition.isEmpty("stype")) {
            String stype = (String) pageCondition.getByKey("stype");
            sqlWhere.append(" and a.stype=?");
            param.add(stype);
        }

        if (pageCondition.isEmpty("username")) {
            String username = (String) pageCondition.getByKey("username");
            sqlWhere.append(" and a.username=?");
            param.add(username);
        }

        if (pageCondition.isEmpty("phone")) {
            String phone = (String) pageCondition.getByKey("phone");
            sqlWhere.append(" and a.phone=?");
            param.add(phone);
        }

        if (pageCondition.isEmpty("rankType")) {
            int rankType = Integer.parseInt((String) pageCondition.getByKey("rankType"));
            sqlWhere.append(" and a.rankType=?");
            param.add(rankType);
        }
        sqlWhere.append(" order by a.addtime desc ");
        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql + sqlWhere + "", param.toArray(), pageCondition.getPage(), column);
        int count = this.queryCountByParam("select count(1) from(" + sql + sqlWhere + ")c", param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }
}
